package com.southgis.ibase.sql.func;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.PagerUtils;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunction;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class CommonFunc implements IExclusiveDatabaseFunc
{
	//==============================
	//初始化及属性
	protected Dialect bindDialect;
	public Dialect getDialect()
	{
		return bindDialect;
	}
	protected String typeName;
	@Override
	public void setDialect(Dialect dialect,String dbType) {
		bindDialect=dialect;
		typeName=dbType;
	}
	//-----------------------------
	
	@Override
	public String charType(int len)
	{
		if(len<0) {
			return getDialect().getTypeName(java.sql.Types.CLOB);
		}else if(len==0) {
			String typeName=getDialect().getTypeName(java.sql.Types.VARCHAR, 1, 0, 0);
			int pos=typeName.indexOf('(');
			if(pos>0) typeName=typeName.substring(0,pos).trim();
			return typeName;
		}else {
			return getDialect().getTypeName(java.sql.Types.VARCHAR, len, 0, 0);
		}
	}
	
	@Override
	public String binaryType(int len)
	{
		if(len<0) {
			return getDialect().getTypeName(java.sql.Types.BLOB);
		}else if(len==0) {
			String typeName=getDialect().getTypeName(java.sql.Types.VARBINARY, 1, 0, 0);
			int pos=typeName.indexOf('(');
			if(pos>0) typeName=typeName.substring(0,pos).trim();
			return typeName;
		}else {
			return getDialect().getTypeName(java.sql.Types.VARBINARY, len, 0, 0);
		}
	}
	
	@Override
	public String datetimeType()
	{
		return getDialect().getTypeName(java.sql.Types.TIMESTAMP);
	}
	
	@Override
	public String doubleType()
	{
		return getDialect().getTypeName(java.sql.Types.DOUBLE);
	}
	
	@Override
	public String decimalType(int len,int scale)
	{
		if(len<=0) return doubleType();
		
		try {
			return getDialect().getTypeName(java.sql.Types.NUMERIC,128,len,scale);
		}catch (Exception e) {
			return getDialect().getTypeName(java.sql.Types.DECIMAL,128,len,scale);
		}
	}
	
	@Override
	public String intType(int width)
	{
		try {
			if(width<2) { //1  位数：3
				try {
					return getDialect().getTypeName(java.sql.Types.TINYINT);//如果不支持时，提升使用smallint
				}catch (Exception e) {
				}
			}
			if(width<4) {//2  位数：5
				try {
					return getDialect().getTypeName(java.sql.Types.SMALLINT);//如果不支持时，提升使用int
				}catch (Exception e) {
				}
			}
			if(width<8) {//4  位数：10
				try {
					return getDialect().getTypeName(java.sql.Types.INTEGER);//一般都会支持。如果不支持时，提升使用bigint
				}catch (Exception e) {
				}
			}
			if(width<16) {//8  位数：19
				try {
					return getDialect().getTypeName(java.sql.Types.BIGINT);//如果不支持时，提升使用decimal(19,0)
				}catch (Exception e) {
					return decimalType(19, 0);
				}
			}
			//width==16 位数：32
			return decimalType(32, 0);
		}catch (Exception e) {
			return getDialect().getTypeName(java.sql.Types.INTEGER); //如果提升类型不支持，默认都为int类型
		}
	}

	
	@Override
	public String notEmptyCharWhere(String fieldName)
	{
		return "(" + fieldName + " is not null and " + fieldName + "<>'')";
	}

	@Override
	public String emptyCharWhere(String fieldName)
	{
		return "(" + fieldName + " is null or " + fieldName + "='')";
	}

	@Override
	public String concatChar(String... charVals)
	{
		return "CONCAT(" + String.join(", ", charVals) + ")";
	}
	
	@Override
	public String subChar(String charVal, int start, int end)
	{
		String funStr=null;
		Map<String, SQLFunction> funcs=getDialect().getFunctions();
		SQLFunction fun=funcs.get("substring");
		if(fun==null) {
			fun=funcs.get("substr");
		}
		if(fun!=null) {
			funStr=formatFunction(fun.toString(), charVal, Integer.toString(start+1),Integer.toString(end-start));
		}
		return funStr;
	}

	@Override
	public String getCharToDateInvoke(String charVal, int fmt)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}
	
	@Override
	public String getYearInvoke(String dateVal)
	{
		String toYear=null;
		SQLFunction fun=getDialect().getFunctions().get("year");
		if(fun!=null) {
			toYear=formatFunction(fun.toString(),dateVal);
		}
		return toYear;
	}
	
	@Override
	public String getDateConst(String fmtVal)
	{
		//兼容字符串是否已添加了单引号
		if(fmtVal.charAt(fmtVal.length()-1)!='\'')
			fmtVal=fmtVal+"'";
		
		if(fmtVal.charAt(0)!='\'') {
			return "date'"+fmtVal;
		}else {
			return "date"+fmtVal;
		}
	}
	
	@Override
	public String getCastInvoke(String orgVal,int targetType,int length, int scale)
	{
		if(length<0)
			length=255;
		if(scale<0)
			scale=8;
		//当前实现有一个bug:没有用空格分隔，导致转换有错。所以加一个空格
		return getDialect().cast(orgVal+" ", targetType, length, length, scale);
	}
	
	@Override
	public String getLowerInvoke(String orgVal)
	{
		return getDialect().getLowercaseFunction()+"("+orgVal+")";
	}
	
	@Override
	public String getUpperInvoke(String orgVal)
	{
		Map<String, SQLFunction> funcs=getDialect().getFunctions();
		String upper=null;
		SQLFunction fun=funcs.get("upper");
		if(fun!=null) {
			upper=formatFunction(fun.toString(), orgVal);
		}
		return upper;
	}
	
	@Override
	public String getIfnullInvoke(String orgVal, String other)
	{
		Map<String, SQLFunction> funcs=getDialect().getFunctions();
		String ifnullStr=null;
		SQLFunction fun=funcs.get("coalesce");
		if(fun!=null) {
			ifnullStr="coalesce";
		}
		if(ifnullStr!=null) {
			fun=funcs.get("nvl");
			if(fun!=null)
				ifnullStr="nvl";
		}		
		if(ifnullStr!=null) {
			fun=funcs.get("ifnull");
			if(fun!=null)
				ifnullStr="ifnull";
		}		
		
		return ifnullStr+"("+orgVal+","+other+")";
	}
	
	@Override
	public String getNowInvoke()
	{
		Map<String, SQLFunction> funcs=getDialect().getFunctions();
		String nowStr=null;
		SQLFunction fun=funcs.get("now");
		if(fun!=null) {
			nowStr="now()";
		}
		if(nowStr!=null) {
			fun=funcs.get("sysdate");
			if(fun!=null)
				nowStr="sysdate()";
		}
		return nowStr;
	}
	
	@Override
	public String pageSql(String sql, int offset, int pageSize)
	{
		try {
			return PagerUtils.limit(sql, DbType.of(typeName), offset, pageSize);
		}catch (Exception e) {
			log.warn("生成分页sql",e);
			return null;
		}
	}
	
	@Override
	public String countSql(String sql)
	{
		try {
			String countSql = getDistinctContSql(sql);
			if (StringUtils.isEmpty(countSql)) {
				countSql = PagerUtils.count(sql, DbType.of(typeName));
			}
			return countSql;
		} catch (Exception e) {
			log.warn("生成统计数sql",e);
			return "select count(*) from ("+sql+") ct_";
		}
	}

	@Override
	public String sqlOfTableCount(String tableName, String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}
	
	@Override
	public String sqlOfListTable(String name, TableTypeEnum type, String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}

	@Override
	public String sqlOfListColumns(String tableName, String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}

	@Override
	public String sqlOfListConstraint(ConstraintTypeEnum type, String tableName,String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}
	
	@Override
	public List<String> sqlOfCreateTable(String tableName, String comment, List<ColumnDefine> colDef, String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}

	@Override
	public String sqlOfModifyTableComment(String comment,String tableName,String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}

	@Override
	public List<String> sqlOfAddColumn(ColumnDefine colDef,String tableName, String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}

	@Override
	public List<String> sqlOfModifyColumn(ModifyColumnDefine colDef,String tableName, String schema)
	{
		throw new UnsupportedOperationException("方法由子类实现");
	}
	
	@Override
	public String sqlOfDropColumn(String colName, String tableName, String schema)
	{
		return String.format("alter table %s drop column %s", getFullTable(schema,tableName),colName);
	}
	
	@Override
	public String sqlOfAddPrimaryKey(String keyName, String colName, String tableName, String schema)
	{
		if(StringUtils.isEmpty(keyName)) {
			String sc=schema+tableName+colName;
			keyName="PK"+Long.toString(System.currentTimeMillis(),36)+Integer.toString(sc.hashCode(),36);
		}
		return String.format("ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY(%s)",
				getFullTable(schema, tableName), keyName, colName);
	}
	
	@Override
	public String sqlOfDropPrimaryKey(String keyName, String tableName, String schema)
	{
		return sqlOfDropConstraint(keyName, tableName, schema);
	}
	
	@Override
	public String sqlOfAddUniqueKey(String keyName, String colName, String tableName, String schema)
	{
		if(StringUtils.isEmpty(keyName)) {
			String sc=schema+tableName+colName;
			keyName="UK"+Long.toString(System.currentTimeMillis(),36)+Integer.toString(sc.hashCode(),36);
		}
		return String.format("ALTER TABLE %s ADD CONSTRAINT %s UNIQUE(%s)",
				getFullTable(schema, tableName), keyName, colName);
	}

	@Override
	public String sqlOfDropUniqueKey(String keyName, String tableName, String schema)
	{
		return sqlOfDropConstraint(keyName, tableName, schema);
	}

	@Override
	public String sqlOfDropForeignKey(String keyName, String tableName, String schema)
	{
		return sqlOfDropConstraint(keyName, tableName, schema);
	}
	
	@Override
	public String sqlOfEnableForeignKey(String keyName, Boolean enabled, String tableName, String schema)
	{
		//使用延时到事务提交时检查。但在启用立即检查时要注意，不能在一个事务中调整。
		return "ALTER TABLE "+getFullTable(schema,tableName)
				+" ALTER CONSTRAINT "+keyName+" "+(enabled?" NOT DEFERRABLE":" DEFERRABLE INITIALLY DEFERRED");
	}


	//================================
	//功能方法封装
	/**
	 * 从定义字符串中获取长度定义信息
	 * @param dataDefine 字义的字符串。格式为：xxx(长度)yyy，或：xxx(长度,小数位)yyy
	 * @return 拆分的定义信息："type"-类型名（数据库相关），"suftype"-类型的后缀，"length"-长度，"scale"-保留小数位
	 */
	public static Map<String,String> getColnumLengthFromDefine(String dataDefine)
	{
		Map<String,String> def=new HashMap<>();
		int pos=dataDefine.indexOf("(");
		String stype=dataDefine;
		String slength="";
		String ssuftype="";
		String sscale="";
		if(pos>=0) {
			stype=dataDefine.substring(0,pos).trim();
			slength=dataDefine.substring(pos+1).trim();
			pos=slength.indexOf(")");
			if(pos>=0) {
				ssuftype=slength.substring(pos+1).trim();
				slength=slength.substring(0,pos);
			}
			pos=slength.indexOf(",");
			if(pos>=0) {
				sscale=slength.substring(pos+1).trim();
				slength=slength.substring(0,pos).trim();
			}
		}
		def.put("type", stype);
		def.put("suftype", ssuftype);
		def.put("length", slength);
		def.put("scale", sscale);
		
		return def;
	}
	
	/**
	 * 通过列定义的类型，生成定义类型的SQL片段
	 * @param colDef
	 * @return
	 */
	protected String getColumnTypeString(ColumnDefine colDef)
	{
		String stype="varchar(255)";
		int len=colDef.getLength();
		switch(colDef.getType())
		{
		case Text:
			if(len==0) len=255;
			stype=charType(len);
			break;
		case Binary:
			if(len==0) len=255;
			stype=binaryType(len);
			break;
		case Byte:
			stype=intType(1);
			break;
		case Integer:
			stype=intType(4);
			break;
		case Bigint:
			stype=intType(8);
			break;
		case Double:
			stype=doubleType();
			break;
		case Decimal:
			stype=decimalType(len,colDef.getScale());
			break;
		case Datetime:
			stype=datetimeType();
			break;
		case Json:
			if(len==0) len=4000;
			stype=charType(len);
			break;
//		case Geometry:
//			if(len==0) len=4000;
//			stype=binaryType(len);
//			break;
		}
		return stype;
	}
	
	/**
	 * 格式化函数调用语句
	 * @param funcTempl 函数模板，参数值使用占位符?1,?2等
	 * @param dataVal 参数值
	 * @return
	 */
	protected String formatFunction(String funcTempl, String... dataVal)
	{
		String ret=funcTempl;
		if(funcTempl.contains("?1")) {
			if(dataVal!=null) {
				for(int ix=1;ix<=dataVal.length;++ix) {
					ret=ret.replace("?"+ix, dataVal[ix-1]);
				}
			}
		}else {
			int pos=funcTempl.indexOf("(",0)+1;
			String param="";
			if(dataVal!=null && dataVal.length>0) {
				param=String.join(",", dataVal);
			}
			if(pos>0) {
				int end=funcTempl.indexOf(")",pos);
				ret=funcTempl.substring(0,pos)+param+funcTempl.substring(end);
			}else {
				ret=funcTempl+"("+param+")";
			}
		}
		return ret;
	}

	/**
	 * 通用的生成删除约束的sql语句
	 * @param keyName
	 * @param tableName
	 * @param schema
	 * @return
	 */
	protected String sqlOfDropConstraint(String keyName, String tableName, String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)+" drop CONSTRAINT "+keyName;
	}
	
	/**
	 * 是否有架构名，返回正确的表名
	 * @param schema
	 * @param table
	 * @return
	 */
	protected String getFullTable(String schema,String table)
	{
		return StringUtils.isEmpty(schema)?table:(schema+"."+table);
	}

	/**
	 * 获取使用DISTINCT关键字的时候统计语句的处理
	 * @param sql 原sql语句
	 * @return 返回统计语句
	 */
	protected String getDistinctContSql(String sql) {
		int formIndex = sql.toUpperCase().indexOf(" FROM ");
		if (0 < formIndex && sql.toUpperCase().substring(0, formIndex).contains(" DISTINCT")) {
			return "select count(*) from ("+sql+") ct_";
		}
		return null;
	}

}
